﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;


/// <summary>
/// Summary description for CommonDS
/// </summary>
public class CommonDS
{
    
    public CommonDS()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public List<ddlItem> DS_Country()
    {
        List<ddlItem> list = new List<ddlItem>();
        MySqlCommand cmd = new MySqlCommand("", Util.con());
        cmd.CommandText = "select Country from tbl_countrystatemapping order by Country";
        MySqlDataAdapter DA = new MySqlDataAdapter(cmd);
        DataSet DS = new DataSet();
        DA.Fill(DS);
        foreach (DataRow dr in DS.Tables[0].Rows)
        {
            list.Add(new ddlItem { Text=dr[0].ToString(), Value=dr[0].ToString() });
        }
        return list;
    }

    public List<ddlItem> DS_State(string country)
    {
        List<ddlItem> list = new List<ddlItem>();
        MySqlCommand cmd = new MySqlCommand("", Util.con());
        cmd.CommandText = "select State from tbl_countrystatemapping where Country='"+ country +"' order by State";
        MySqlDataAdapter DA = new MySqlDataAdapter(cmd);
        DataSet DS = new DataSet();
        DA.Fill(DS);
        foreach (DataRow dr in DS.Tables[0].Rows)
        {
            list.Add(new ddlItem { Text = dr[0].ToString(), Value = dr[0].ToString() });
        }
        return list;
    }

    public List<ddlItem> DS_City(string country, string state)
    {
        List<ddlItem> list = new List<ddlItem>();
        MySqlCommand cmd = new MySqlCommand("", Util.con());
        cmd.CommandText = "select City from tbl_countrystatemapping where Country='"+ country +"' and State='"+ state +"' order by City";
        MySqlDataAdapter DA = new MySqlDataAdapter(cmd);
        DataSet DS = new DataSet();
        DA.Fill(DS);
        foreach (DataRow dr in DS.Tables[0].Rows)
        {
            list.Add(new ddlItem { Text = dr[0].ToString(), Value = dr[0].ToString() });
        }
        return list;
    }

}

public class ddlItem
{
    public string Text { get; set; }
    public string Value { get; set; }
}